﻿using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using PM.Code.DAL;
using Wuqi.Webdiyer;
namespace PM.PMSys
{
        /// <summary>
        /// 数据访问类:news
        /// </summary>
        public class news
        {
            public news()
            { }
            #region  成员方法
            /// <summary>
            /// 是否存在该新闻
            /// </summary>
            public bool Exists(int id)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(1) from Sys_News");
                strSql.Append(" where id=@id ");
                SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
                parameters[0].Value = id;

                return DbHelper.Exists(strSql.ToString(), parameters);
            }


            /// <summary>
            /// 增加一条新闻
            /// <param name="type">新闻类型</param>
            /// <param name="title">新闻标题</param>
            /// <param name="newscontent">新闻内容</param>
            /// <param name="date">新闻日期</param>
            /// </summary>
            public int Add(string type,string title,string newscontent,string date)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into Sys_News(");
                strSql.Append("type,title,newscontent,date)");
                strSql.Append(" values (");
                strSql.Append("@type,@title,@newscontent,@date)");
                strSql.Append(";select @@IDENTITY");
                SqlParameter[] parameters = {
					new SqlParameter("@type", SqlDbType.NVarChar,10),
					new SqlParameter("@title", SqlDbType.NVarChar,50),
					new SqlParameter("@newscontent", SqlDbType.NVarChar),
					new SqlParameter("@date", SqlDbType.DateTime)};
                parameters[0].Value = type;
                parameters[1].Value = title;
                parameters[2].Value = newscontent;
                parameters[3].Value = date;
                return DbHelper.ExecuteSql(strSql.ToString(), parameters);
            }
            /// <summary>
            /// 更新一条新闻
            /// <param name="type">新闻类型</param>
            /// <param name="title">新闻标题</param>
            /// <param name="newscontent">新闻内容</param>
            /// <param name="date">新闻日期</param>
            /// <param name="id">新闻ID</param>
            /// </summary>
            public bool Update(string type, string title, string newscontent, string date,int id)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Sys_News set ");
                strSql.Append("type=@type,");
                strSql.Append("title=@title,");
                strSql.Append("newscontent=@newscontent,");
                strSql.Append("date=@date");
                strSql.Append(" where id=@id");
                SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@type", SqlDbType.NVarChar,10),
					new SqlParameter("@title", SqlDbType.NVarChar,50),
					new SqlParameter("@newscontent", SqlDbType.NVarChar),
					new SqlParameter("@date", SqlDbType.DateTime)};
                parameters[0].Value = id;
                parameters[1].Value = type;
                parameters[2].Value = title;
                parameters[3].Value = newscontent;
                parameters[4].Value = date;

                int rows = DbHelper.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            public int checkHighlight(int id)
            {
                string thestatus = "";
                int a=99 ;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select highlight from Sys_News");
                strSql.Append(" where id=@id");
                SqlParameter[] parameters = {
                   new SqlParameter("@id", SqlDbType.Int)};
                parameters[0].Value = id;
                SqlDataReader reader = PM.Code.DAL.DbHelper.ExecuteDataReaderBySql(strSql.ToString(), parameters);
                while (reader.Read())
                {
                    try
                    {
                        thestatus = reader[0].ToString();
                        a = int.Parse(thestatus);
                    }
                    catch { }
                }
                reader.Close();
                return a;
            }

            /// <summary>
            /// 改变新闻高亮状态
            /// </summary>
            /// <param name="id">新闻id</param>
            /// <returns></returns>
            public int doHighlight(int id)
            {
                int checkresult=checkHighlight(id);
                if (checkresult == 99)
                {
                    return 99;
                }
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Sys_News set ");
                if (checkresult==1)
                {
                    strSql.Append("highlight=0");
                }
                else
                {
                    strSql.Append("highlight=1");
                }
                strSql.Append(" where id=@id");
                SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
                parameters[0].Value = id;
                int rows = DbHelper.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    return 1;
                }
                else
                {
                    return 0;
                }
            }

            /// <summary>
            /// 删除一条数据
            /// </summary>
            public bool Delete(int id)
            {

                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from Sys_News ");
                strSql.Append(" where id=@id");
                SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
};
                parameters[0].Value = id;

                int rows = DbHelper.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            /// <summary>
            /// 删除一条数据（id列表）
            /// </summary>
            public bool DeleteList(string idlist)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from Sys_News ");
                strSql.Append(" where id in (" + idlist + ")  ");
                int rows = DbHelper.ExecuteSql(strSql.ToString());
                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            /// <summary>
            /// 获得数据列表(分页控件)
            /// <param name="strWhere">查询条件</param>
            /// <param name="pageSize">每页显示数据数</param>
            /// <param name="pager">Aspnetpager</param>
            /// </summary>
            public DataTable GetList(string strWhere, int pageSize, AspNetPager pager)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select id,type,title,date,highlight ");
                strSql.Append(" FROM Sys_News ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" where " + strWhere);
                }
                strSql.Append(" order by date desc");
                return DbHelper.GetPagedDataTable(strSql.ToString(), pageSize, pager);
            }

            /// <summary>
            /// 获得数据
            /// <param name="id">查询id</param>
            /// </summary>
            /// <returns>Datareader</returns>
            public SqlDataReader GetDetail(int id)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("select * FROM Sys_News where id={0}", id);
                return DbHelper.ExecuteDataReaderBySql(strSql.ToString());
            }
            /// <summary>
            /// 获得前几行数据
            /// <param name="Top">前几行数据</param>
            /// <param name="strWhere">where条件语句(where不用写)</param>
            /// <param name="filedOrder">排序字段(asc,desc要自己写)</param>
            /// </summary>
            public DataTable GetList(int Top, string strWhere, string filedOrder)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select ");
                if (Top > 0)
                {
                    strSql.Append(" top " + Top.ToString());
                }
                strSql.Append(" id,type,title,date,highlight ");
                strSql.Append(" FROM Sys_News ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" where " + strWhere);
                }
                strSql.Append(" order by " + filedOrder);
                return DbHelper.ExecuteDataTableBySql(strSql.ToString());
            }

            public void CreateMiniNews(ref StringBuilder projectnews,ref StringBuilder mininews)
            {
                SqlDataReader myreader = null;
                StringBuilder sqlstr = new StringBuilder();
                sqlstr.Append("select top 3 b.btnname,a.plevel,a.ptype,a.opendate,a.closedate from");
                sqlstr.Append("  Bus_ProjectPlan a inner join Sys_Guide b on a.[type]=b.id where closedate>getdate() order by opendate desc  ");
                sqlstr.Append("select top 3 id,[type],title,Convert(varchar(10),[date],111) as creatdate from Sys_News order by date");
                myreader = PM.Code.DAL.DbHelper.ExecuteDataReaderBySql(sqlstr.ToString());
                if (myreader.HasRows)
                {
                    projectnews.Append("<table><tr><td style=\"width:120px\">类型</td><td style=\"width:120px\">级别+类别</td>");
                    projectnews.Append("<td style=\"width:160px\">开启时间</td><td style=\"width:160px\">关闭时间</td></tr> ");
                    while (myreader.Read())
                    {
                        projectnews.Append("<tr><td>").Append(myreader[0].ToString()).Append("</td>");
                        projectnews.Append("<td>[").Append(myreader[1].ToString()).Append("][").Append(myreader[2].ToString()).Append("]</td>");
                        projectnews.Append("<td>").Append(myreader[3].ToString()).Append("</td>");
                        projectnews.Append("<td>").Append(myreader[4].ToString()).Append("</td></tr>");
                    }
                }
                else
                    projectnews.Append("无任何项目信息！");
                myreader.NextResult();
                if (myreader.HasRows)
                {
                    mininews.Append("<table>");
                    while (myreader.Read())
                    {
                        mininews.Append("<tr><td style='width:50px;'>[").Append(myreader[1].ToString()).Append("]</td>");
                        mininews.Append("<td style='width:350px;'><a target='_blank' href='../News/Newsdetail.aspx?id=").Append(myreader[0].ToString()).Append("'>").Append(myreader[2].ToString()).Append("</a></td>");
                        mininews.Append("<td style='width:100px;'>").Append(myreader[3].ToString()).Append("</td></tr>");
                    }
                }
                else
                    mininews.Append("无任何新闻！");
                projectnews.Append("</table>");
                mininews.Append("</table>");
                myreader.Close();
            }
            #endregion
        }
}